{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "toc": true
   },
   "source": [
    "<h1>目录<span class=\"tocSkip\"></span></h1>\n",
    "<div class=\"toc\"><ul class=\"toc-item\"><li><span><a href=\"#使用子查询进行过滤\" data-toc-modified-id=\"使用子查询进行过滤-1\"><span class=\"toc-item-num\">1&nbsp;&nbsp;</span>使用子查询进行过滤</a></span></li><li><span><a href=\"#作为计算字段使用子查询\" data-toc-modified-id=\"作为计算字段使用子查询-2\"><span class=\"toc-item-num\">2&nbsp;&nbsp;</span>作为计算字段使用子查询</a></span></li></ul></div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "import mysql.connector\n",
    "import pandas as pd\n",
    "conn = mysql.connector.connect(host='127.0.0.1',\n",
    "                      user='iroan',\n",
    "                      password='iroanMYS47',\n",
    "                      database = 'practice_sql')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>prod_id</th>\n",
       "      <th>vend_id</th>\n",
       "      <th>prod_name</th>\n",
       "      <th>prod_price</th>\n",
       "      <th>prod_desc</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BNBG01</td>\n",
       "      <td>DLL01</td>\n",
       "      <td>Fish bean bag toy</td>\n",
       "      <td>3.49</td>\n",
       "      <td>Fish bean bag toy, complete with bean bag worm...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BNBG02</td>\n",
       "      <td>DLL01</td>\n",
       "      <td>Bird bean bag toy</td>\n",
       "      <td>3.49</td>\n",
       "      <td>Bird bean bag toy, eggs are not included</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BNBG03</td>\n",
       "      <td>DLL01</td>\n",
       "      <td>Rabbit bean bag toy</td>\n",
       "      <td>3.49</td>\n",
       "      <td>Rabbit bean bag toy, comes with bean bag carrots</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BR01</td>\n",
       "      <td>BRS01</td>\n",
       "      <td>8 inch teddy bear</td>\n",
       "      <td>5.99</td>\n",
       "      <td>8 inch teddy bear, comes with cap and jacket</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BR02</td>\n",
       "      <td>BRS01</td>\n",
       "      <td>12 inch teddy bear</td>\n",
       "      <td>8.99</td>\n",
       "      <td>12 inch teddy bear, comes with cap and jacket</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>BR03</td>\n",
       "      <td>BRS01</td>\n",
       "      <td>18 inch teddy bear</td>\n",
       "      <td>11.99</td>\n",
       "      <td>18 inch teddy bear, comes with cap and jacket</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>RGAN01</td>\n",
       "      <td>DLL01</td>\n",
       "      <td>Raggedy Ann</td>\n",
       "      <td>4.99</td>\n",
       "      <td>18 inch Raggedy Ann doll</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>RYL01</td>\n",
       "      <td>FNG01</td>\n",
       "      <td>King doll</td>\n",
       "      <td>9.49</td>\n",
       "      <td>12 inch king doll with royal garments and crown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>RYL02</td>\n",
       "      <td>FNG01</td>\n",
       "      <td>Queen doll</td>\n",
       "      <td>9.49</td>\n",
       "      <td>12 inch queen doll with royal garments and crown</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  prod_id vend_id            prod_name  prod_price  \\\n",
       "0  BNBG01   DLL01    Fish bean bag toy        3.49   \n",
       "1  BNBG02   DLL01    Bird bean bag toy        3.49   \n",
       "2  BNBG03   DLL01  Rabbit bean bag toy        3.49   \n",
       "3    BR01   BRS01    8 inch teddy bear        5.99   \n",
       "4    BR02   BRS01   12 inch teddy bear        8.99   \n",
       "5    BR03   BRS01   18 inch teddy bear       11.99   \n",
       "6  RGAN01   DLL01          Raggedy Ann        4.99   \n",
       "7   RYL01   FNG01            King doll        9.49   \n",
       "8   RYL02   FNG01           Queen doll        9.49   \n",
       "\n",
       "                                           prod_desc  \n",
       "0  Fish bean bag toy, complete with bean bag worm...  \n",
       "1           Bird bean bag toy, eggs are not included  \n",
       "2   Rabbit bean bag toy, comes with bean bag carrots  \n",
       "3       8 inch teddy bear, comes with cap and jacket  \n",
       "4      12 inch teddy bear, comes with cap and jacket  \n",
       "5      18 inch teddy bear, comes with cap and jacket  \n",
       "6                           18 inch Raggedy Ann doll  \n",
       "7    12 inch king doll with royal garments and crown  \n",
       "8   12 inch queen doll with royal garments and crown  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\n",
    "'''select * from Products;'''\n",
    ",conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 使用子查询进行过滤"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_num</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>20007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20008</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   order_num\n",
       "0      20007\n",
       "1      20008"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\n",
    "'''select order_num from OrderItems where prod_id = 'RGAN01';'''\n",
    ",conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cust_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1000000004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1000000005</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      cust_id\n",
       "0  1000000004\n",
       "1  1000000005"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\n",
    "'''select cust_id from Orders where order_num in (20007,20008);'''\n",
    ",conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cust_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1000000004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1000000005</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      cust_id\n",
       "0  1000000004\n",
       "1  1000000005"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\n",
    "'''select cust_id from Orders where order_num in (select order_num from OrderItems where prod_id = 'RGAN01');'''\n",
    ",conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cust_name</th>\n",
       "      <th>cust_contact</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Fun4All</td>\n",
       "      <td>Denise L. Stephens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>The Toy Store</td>\n",
       "      <td>Kim Howard</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       cust_name        cust_contact\n",
       "0        Fun4All  Denise L. Stephens\n",
       "1  The Toy Store          Kim Howard"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\n",
    "'''\n",
    "select cust_name,cust_contact from Customers where cust_id in \n",
    "(select cust_id from Orders where order_num in \n",
    "(select order_num from OrderItems where prod_id = 'RGAN01'\n",
    ")\n",
    ");'''\n",
    ",conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 作为计算字段使用子查询"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cust_name</th>\n",
       "      <th>cust_state</th>\n",
       "      <th>orders</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Kids Place</td>\n",
       "      <td>OH</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Fun4All</td>\n",
       "      <td>AZ</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>The Toy Store</td>\n",
       "      <td>IL</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Fun4All</td>\n",
       "      <td>IN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Village Toys</td>\n",
       "      <td>MI</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       cust_name cust_state  orders\n",
       "0     Kids Place         OH       0\n",
       "1        Fun4All         AZ       1\n",
       "2  The Toy Store         IL       1\n",
       "3        Fun4All         IN       1\n",
       "4   Village Toys         MI       2"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(\n",
    "'''select cust_name,cust_state,\n",
    "(select count(*) from Orders where Orders.cust_id = Customers.cust_id) as orders\n",
    "from Customers order by orders;'''\n",
    ",conn)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "base"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {
    "height": "12px",
    "width": "163px"
   },
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "目录",
   "title_sidebar": "大纲",
   "toc_cell": true,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
